package com.ruoyi.pingjiao.utils;


import com.ruoyi.pingjiao.domain.Score;
import com.ruoyi.pingjiao.mapper.ScoreMapper;
import com.ruoyi.pingjiao.service.IStudentService;
import com.ruoyi.pingjiao.service.ScoreService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

@Slf4j

public class ExcelUtil {

    @Autowired
    private ScoreMapper scoreMapper;
    @Autowired
    private IStudentService studentService;

    public  void importExcel(String file)  {
//        String filePath = "C:\\Sxl.\\达成度分析测试用例.xlsx";
        int sheetIndex = 1; // 工作表索引，从0开始
        int headerRows = 6; // 表头行数，假设为2
        List<HashMap<Integer,String>> hand = getHand(file, sheetIndex, headerRows);
        List<List<String>> data = readExcel(file, sheetIndex, headerRows);
//        Integer studentId=null;
        System.out.println(hand);
//

// 遍历数据
        int sum1=0;
        int b=0;
        List<List<Double>>arrayList=new ArrayList<>();
        List<Double>arrayList1=new ArrayList<>();
        List<Double>averageList=new ArrayList<>();
        for (List<String> rowData : data) {
//            System.out.println(hand);
//            for (String cellData : rowData) {
//                System.out.print(cellData + "\t");
//            }

            System.out.println();
            String studentNo=rowData.get(1);
            String studentName=rowData.get(2);
            int count=0;
            double sum=0;

            for (int i=0;i<hand.size();i++){
                List<Integer>list=new ArrayList<>();
                HashMap<Integer, String> integerStringHashMap = hand.get(i);
                Integer[] arr = integerStringHashMap.keySet().toArray(new Integer[0]);
                Arrays.sort(arr);

                for(int j=0;j<hand.get(i).size();j++){
                    int a=0;
                   List<Double>num=new ArrayList<>();
                   List<Double>nums=new ArrayList<>();
                    Score score=new Score();
//                    score.setCoureassessId(i+1);
                    score.setStudentNo(studentNo);
                    score.setStudentName(studentName);
//                    List<Integer> integers = hand.get(i);
                    if(Objects.equals(rowData.get(arr[j]), "")){
                        //完成率
                        score.setWokeName(integerStringHashMap.get(arr[j]));
//                        score.setStudentId(studentId);
//                        Double aDouble = MathData.finishData(list, i+1,);
//                        score.setScore((aDouble));

//                        sum=sum+MathData.finishData(list,i+1);
                        if(integerStringHashMap.get(arr[j]).equals("学生课程达成度")){
                            score.setScore(sum/count);
                            score.setCoureassessId(i+1);

                        }else {
                            count++;
                            score.setCoureassessId(i+1);
//                            score.setScore((aDouble));

                        }
                        //添加到数据库
                        scoreMapper.save(score);
                   }else{
                        //平时作业
                        score.setCoureassessId(i+1);
                       score.setWokeName(integerStringHashMap.get(arr[j]));
                       score.setScore(Double.valueOf(rowData.get(arr[j])));
//                       score.setStudentNo(rowData.get(arr[j]));
                        while (a<data.size()&&b==0){
                            if(!Objects.equals(rowData.get(arr[j]), "")){
                                num.add(Double.valueOf(data.get(a).get(arr[j])));
                            }
                            a=a+1;
                        }
                        System.out.println(num);
                        num.stream().filter(Objects::nonNull).forEach(nums::add);
                        System.out.println(nums);
                        Double average = MathData.average(nums);
                        arrayList1.add(average);
//                        arrayList.add(num);
//                       score.setStudentId(studentId);
                       list.add(Integer.valueOf(rowData.get(arr[j])));
                       scoreMapper.save(score);
                   }
                }
                Double average = MathData.average(arrayList1);
                averageList.add(average);

            }
            b=b+1;

        }
//        arrayList.add(arrayList1);
//        arrayList.add(averageList);
        System.out.println(arrayList1);
        System.out.println(averageList);
        System.out.println(arrayList);
    }
    public static List<List<String>> readExcel(String filePath,int sheetIndex,int headerRows) {
        List<List<String>> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取指定索引的工作表
            Iterator<Row> rowIterator = sheet.iterator();
            // 处理表头行
            for (int i = 0; i < headerRows; i++) {
                if (rowIterator.hasNext()) {
                    rowIterator.next(); // 跳过表头行
                } else {
                    break;
                }
        }
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                List<String> rowData = new ArrayList<>();
                for (Cell cell : row) {
                    String cellValue = getMergedCellValue(sheet, cell);
                    rowData.add(cellValue);
                }
                if(Objects.equals(rowData.get(0), "")){
                    break;
                }
                data.add(rowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return data;
    }
    public static List<HashMap<Integer,String>>getHand(String filePath, int sheetIndex, int headerRows)  {
        List<HashMap<Integer,String>> list = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取指定索引的工作表
            Iterator<Row> rowIterator = sheet.iterator();
            String s1="课程目标（1）";
            String s2="课程目标（2）";
            String s3="课程目标（3）";
            String s4="课程目标（4）";
            String s5="学生课程达成度";
//            String s6="学号";
//            String s7="姓名";
            HashMap<Integer,String>arr1=new HashMap<Integer,String>();
            HashMap<Integer,String>arr2=new HashMap<Integer,String>();
            HashMap<Integer,String>arr3=new HashMap<Integer,String>();
            HashMap<Integer,String>arr4=new HashMap<Integer,String>();
            HashMap<Integer,String>arr5=new HashMap<Integer,String>();
//            HashMap<Integer,String>arr6=new HashMap<Integer,String>();
//            HashMap<Integer,String>arr7=new HashMap<Integer,String>();
            // 处理表头行
            for (int i = 4; i < 5; i++) {
                if (rowIterator.hasNext()) {
//                    rowIterator.next(); // 跳过表头行
                    Row row = sheet.getRow(i);
                    for (Cell cell : row) {
                        String mergedCellValue = getMergedCellValue(sheet,cell);
                        if (mergedCellValue.contains(s1)) {
                            for (int a = 0; a < 4; a++) {
                                int columnIndex = cell.getColumnIndex() + a;
                                Row row1 = sheet.getRow(i + 1);
                                Cell cell1 = row1.getCell(columnIndex);
                                arr1.put(columnIndex, cell1.getStringCellValue());
                            }
                        } else if (mergedCellValue.contains(s2)) {
                            for (int a = 0; a < 3; a++) {
                                int columnIndex = cell.getColumnIndex() + a;
                                Row row1 = sheet.getRow(i + 1);
                                Cell cell1 = row1.getCell(columnIndex);
                                arr2.put(columnIndex, cell1.getStringCellValue());
                            }
                        } else if (mergedCellValue.contains(s3)) {
                            for (int a = 0; a < 3; a++) {
                                int columnIndex = cell.getColumnIndex() + a;
                                Row row1 = sheet.getRow(i + 1);
                                Cell cell1 = row1.getCell(columnIndex);
                                arr3.put(columnIndex, cell1.getStringCellValue());
                            }
                        } else if (mergedCellValue.contains(s4)) {
                            for (int a = 0; a < 4; a++) {
                                int columnIndex = cell.getColumnIndex() + a;
                                Row row1 = sheet.getRow(i + 1);
                                Cell cell1 = row1.getCell(columnIndex);
                                arr4.put(columnIndex, cell1.getStringCellValue());
                            }
                        }else if(mergedCellValue.contains(s5)){
                            int columnIndex = cell.getColumnIndex();
                            Row row1 = sheet.getRow(i);
                            Cell cell1 = row1.getCell(columnIndex);
                            arr5.put(columnIndex,cell1.getStringCellValue());
                        }
//                        else if(mergedCellValue.contains(s6)){
//                            int columnIndex = cell.getColumnIndex();
//                            Row row1 = sheet.getRow(i+1);
//                            Cell cell1 = row1.getCell(columnIndex);
//                            arr6.put(columnIndex,cell1.getStringCellValue());
//                        }else if(mergedCellValue.contains(s7)){
//                            int columnIndex = cell.getColumnIndex();
//                            Row row1 = sheet.getRow(i+1);
//                            Cell cell1 = row1.getCell(columnIndex);
//                            arr7.put(columnIndex,cell1.getStringCellValue());
//                        }
                    }
                }
            }
            list.add(arr1);
            list.add(arr2);
            list.add(arr3);
            list.add(arr4);
            list.add(arr5);
//            list.add(arr6);
//            list.add(arr7);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
        private static String getMergedCellValue(Sheet sheet, Cell cell) {
        int rowIndex = cell.getRowIndex();
        int columnIndex = cell.getColumnIndex();

        for (CellRangeAddress range : sheet.getMergedRegions()) {
            if (range.isInRange(rowIndex, columnIndex)) {
                Row firstRow = sheet.getRow(range.getFirstRow());
                Cell mergedCell = firstRow.getCell(range.getFirstColumn());
                return mergedCell.getStringCellValue();
            }
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }
}
